package org.smart4j.framework.common.util;

import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.lang3.StringUtils;
import org.smart4j.framework.common.helper.ConfigHelper;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

/**
 * 数据库操作工具类
 *
 * @author YooLin1c
 * @version 1.0.0
 * @since 2018-02-04 13:56
 */
@Slf4j
public class DBUtil {
    /**
     * 定义一个用于放置数据库连接的局部线程变量（使每个线程都拥有自己的连接，线程之间隔离连接，防止互相干扰）
     * 每个线程应都有自己的连接，而不是共享同一个连接，否则线程A执行完后有可能关闭连接，
     * 而此时线程B正在使用该连接执行SQL，此时线程B就会报数据库连接关闭异常！
     * <p>
     * 优化：使用连接池管理数据库连接
     */
    private static final ThreadLocal<Connection> CONNECTION_HOLDER;
    private static final QueryRunner QUERY_RUNNER;
    private static final BasicDataSource DATA_SOURCE;

    static {
        CONNECTION_HOLDER = new ThreadLocal<>();
        QUERY_RUNNER = new QueryRunner();
        DATA_SOURCE = new BasicDataSource();

        String driver = ConfigHelper.getJdbcDriver();
        String url = ConfigHelper.getJdbcUrl();
        String username = ConfigHelper.getJdbcUsername();
        String password = ConfigHelper.getJdbcPassword();
        DATA_SOURCE.setDriverClassName(driver);
        DATA_SOURCE.setUrl(url);
        DATA_SOURCE.setUsername(username);
        DATA_SOURCE.setPassword(password);
    }

    /**
     * 获取数据库连接
     *
     * @return 连接
     */
    public static Optional<Connection> getConnection() {
        Connection connection = CONNECTION_HOLDER.get();
        if (Objects.isNull(connection)) {
            try {
                connection = DATA_SOURCE.getConnection();
            } catch (SQLException e) {
                log.error("get connection from dbcp2 failed!", e);
                throw new RuntimeException(e);
            } finally {
                CONNECTION_HOLDER.set(connection);
            }
        }
        return Optional.ofNullable(connection);
    }

    /**
     * 释放数据库连接
     */
    public static void closeConnection() {
        Connection connection = CONNECTION_HOLDER.get();
        if (Objects.isNull(connection)) {
            log.warn("there is no connection need to close!");
            return;
        }
        try {
            connection.close();
        } catch (SQLException e) {
            log.error("connection close failed!", e);
            throw new RuntimeException(e);
        } finally {
            CONNECTION_HOLDER.remove();
        }
    }

    /**
     * 查询获取实体集合
     *
     * @param clazz  实体类
     * @param sql    查询语句
     * @param params 查询参数
     * @param <T>    实体泛型
     * @return 实体集合
     */
    public static <T> List<T> queryBeanList(Class<T> clazz, String sql, Object... params) {
        List<T> result;
        try {
            result = QUERY_RUNNER.query(sql, new BeanListHandler<>(clazz), params);
        } catch (SQLException e) {
            log.error("query List<Bean> failed!", e);
            throw new RuntimeException(e);
        } finally {
            closeConnection();
        }
        return Objects.isNull(result) ? Lists.newArrayList() : result;
    }

    /**
     * 查询单个实体
     *
     * @param clazz  实体类
     * @param sql    查询语句
     * @param params 查询参数
     * @param <T>    实体泛型
     * @return 实体
     */
    public static <T> Optional<T> queryOne(Class<T> clazz, String sql, Object... params) {
        T result;
        try {
            result = QUERY_RUNNER.query(sql, new BeanHandler<>(clazz), params);
        } catch (SQLException e) {
            log.error("query Bean failed!", e);
            throw new RuntimeException(e);
        } finally {
            closeConnection();
        }
        return Optional.ofNullable(result);
    }

    /**
     * 获取数据关系映射集合
     *
     * @param sql    查询语句
     * @param params 查询条件
     * @return 数据关系映射集合
     */
    public static List<Map<String, Object>> queryMapList(String sql, Object... params) {
        List<Map<String, Object>> result;
        try {
            result = QUERY_RUNNER.query(sql, new MapListHandler(), params);
        } catch (SQLException e) {
            log.error("query List<Map<String,Object>> failed!", e);
            throw new RuntimeException(e);
        } finally {
            closeConnection();
        }
        return Objects.isNull(result) ? Lists.newArrayList() : result;
    }

    /**
     * 更新操作（包括insert、delete、update操作）
     *
     * @param sql    更新语句
     * @param params 更新参数
     * @return true 更新成功 false 更新失败
     */
    public static boolean executeUpdate(String sql, Object... params) {
        int row;
        try {
            row = QUERY_RUNNER.update(sql, params);
        } catch (SQLException e) {
            log.error("executeUpdate failed!", e);
            throw new RuntimeException(e);
        } finally {
            closeConnection();
        }
        return row > 0;
    }

    /**
     * 插入实体
     *
     * @param clazz    实体类
     * @param fieldMap 实体属性关系映射
     * @param <T>      实体泛型
     * @return true 插入成功 false 插入失败
     */
    public static <T> boolean insert(Class<T> clazz, Map<String, Object> fieldMap) {
        if (MapUtils.isEmpty(fieldMap)) {
            log.warn("there is no need to insert because field map is empty!");
            return false;
        }
        StringBuilder sql = new StringBuilder("INSERT INTO ");
        StringBuilder values = new StringBuilder();
        sql.append(getTableName(clazz)).append(" (");
        for (String fieldName : fieldMap.keySet()) {
            sql.append(fieldName).append(",");
            values.append("?,");
        }
        sql.deleteCharAt(sql.length() - 1);
        values.deleteCharAt(values.length() - 1);
        sql.append(") VALUES (");
        sql.append(values).append(");");
        return executeUpdate(sql.toString(), fieldMap.values().toArray());
    }

    /**
     * 更新实体
     *
     * @param clazz    实体类
     * @param id       实体主键ID
     * @param fieldMap 实体属性关系映射
     * @param <T>      实体泛型
     * @return true 更新成功 false 更新失败
     */
    public static <T> boolean update(Class<T> clazz, Serializable id, Map<String, Object> fieldMap) {
        if (MapUtils.isEmpty(fieldMap)) {
            log.warn("there is no need to update because field map is empty!");
            return false;
        }
        StringBuilder sql = new StringBuilder("UPDATE ");
        sql.append(getTableName(clazz)).append(" SET ");
        for (String fieldName : fieldMap.keySet()) {
            sql.append(fieldName).append("= ?, ");
        }
        sql.deleteCharAt(sql.length() - 1).append("WHERE id = ?;");
        Collection<Object> values = fieldMap.values();
        values.add(id);
        return executeUpdate(sql.toString(), values.toArray());
    }

    /**
     * 删除实体
     *
     * @param clazz 实体类
     * @param id    实体主键ID
     * @param <T>   实体泛型
     * @return true 删除成功 false 删除失败
     */
    public static <T> boolean delete(Class<T> clazz, Serializable id) {
        String sql = "DELETE FROM ".concat(getTableName(clazz)).concat(" WHERE id = ?;");
        return executeUpdate(sql, id);
    }

    /**
     * 根据实体类名获取表名 TODO 注解化
     *
     * @param clazz 实体类
     * @return 表名
     */
    private static String getTableName(Class clazz) {
        //默认表名前缀 TODO 配置化
        String tableName = "t";
        String className = clazz.getSimpleName();
        if (StringUtils.isBlank(className)) {
            throw new RuntimeException("get a wrong table name from class ".concat(clazz.getSimpleName()).concat("!"));
        }
        for (char ch : className.toCharArray()) {
            if (Character.isUpperCase(ch)) {
                tableName = tableName.concat("_").concat(String.valueOf(Character.toLowerCase(ch)));
            } else {
                tableName = tableName.concat(String.valueOf(ch));
            }
        }
        return tableName;
    }

    /**
     * 开启事务
     */
    public static void beginTransaction() {
        Connection connection = getConnection().get();
        try {
            //关闭事务自动提交（事务默认为自动提交）
            connection.setAutoCommit(false);
            connection.commit();
        } catch (SQLException e) {
            log.error("begin transaction failed!", e);
            throw new RuntimeException(e);
        } finally {
            //将开启事务的连接绑定至当前线程
            CONNECTION_HOLDER.set(connection);
        }
    }

    /**
     * 提交事务
     */
    public static void commitTransaction() {
        Connection connection = getConnection().get();
        try {
            connection.commit();
        } catch (SQLException e) {
            log.error("commit transaction failed!", e);
            throw new RuntimeException(e);
        }
        closeConnection();
    }

    /**
     * 回滚事务
     */
    public static void rollbackTransaction() {
        Connection connection = getConnection().get();
        try {
            connection.rollback();
        } catch (SQLException e) {
            log.error("roll back transaction failed!", e);
            throw new RuntimeException(e);
        }
        closeConnection();
    }
}
